using System;
using System.Data;
using System.Text;
using System.Windows.Forms;
using ctsu.Data;
using ctsu.Shared;


namespace ctsu.Admin.Forms
{
	public class ReportDataIntegrity : System.Windows.Forms.Form
	{
		private DataTable _dtReport;
		private DataView _dvReport;

		private System.Windows.Forms.GroupBox groupBox1;
		private System.Windows.Forms.Button btnRun;
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.ComboBox cboType;
		private System.Windows.Forms.Label label2;
		private System.Windows.Forms.Label label3;
		private System.Windows.Forms.TextBox Count;
		private System.Windows.Forms.Label label4;
		private System.Windows.Forms.DataGrid dg;
		private System.Windows.Forms.Button btnExit;
		private System.Windows.Forms.Button btnPrint;
		private Syncfusion.Windows.Forms.Tools.DateTimePickerAdv FromDate;
		private Syncfusion.Windows.Forms.Tools.DateTimePickerAdv ToDate;
		private System.ComponentModel.Container components = null;

		public ReportDataIntegrity()
		{
			InitializeComponent();
			InitializeDataTable();
			this._dvReport = new DataView(this._dtReport);
			dg.DataSource = this._dvReport;
			InitializeGrid();
			cboType.SelectedIndex = 0;
			this.FromDate.Value = DateTime.Today;
			this.FromDate.IsNullDate = true;
			this.ToDate.Value = DateTime.Today;
			this.ToDate.IsNullDate = true;

		}

		private void InitializeGrid()
		{
			//size grid columns				
			DataGridTableStyle dgts = new DataGridTableStyle();
			dgts.MappingName = "Report";
			dg.TableStyles.Add(dgts);

			for (int i = 0; i < this._dtReport.Columns.Count; i++)
			{
				dgts.GridColumnStyles[i].Width = (int)_dtReport.Columns[i].ExtendedProperties["Width"];
				dgts.GridColumnStyles[i].HeaderText = _dtReport.Columns[i].Caption;
			}
		}

		private void InitializeDataTable()
		{
			this._dtReport = new DataTable("Report");
			DataColumn dc = new DataColumn("ID", typeof(int));
			dc.Caption = "ID";
			dc.ExtendedProperties.Add("Width", 25);
			_dtReport.Columns.Add(dc);

			dc = new DataColumn("ReferenceID", typeof(string));
			dc.Caption = "Reference ID";
			dc.ExtendedProperties.Add("Width", 125);
			_dtReport.Columns.Add(dc);

			dc = new DataColumn("Type", typeof(String));
			dc.Caption = "Type";
			dc.ExtendedProperties.Add("Width", 100);
			_dtReport.Columns.Add(dc);

			dc = new DataColumn("VisitDate", typeof(DateTime));
			dc.Caption = "Visit/Discharge";
			dc.ExtendedProperties.Add("Width", 100);
			_dtReport.Columns.Add(dc);

			dc = new DataColumn("Message", typeof(string));
			dc.Caption = "Message";
			dc.ExtendedProperties.Add("Width", 290);
			_dtReport.Columns.Add(dc);
		}

		protected override void Dispose(bool disposing)
		{
			if (disposing)
			{
				if (components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose(disposing);
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			this.groupBox1 = new System.Windows.Forms.GroupBox();
			this.Count = new System.Windows.Forms.TextBox();
			this.label4 = new System.Windows.Forms.Label();
			this.ToDate = new Syncfusion.Windows.Forms.Tools.DateTimePickerAdv();
			this.label3 = new System.Windows.Forms.Label();
			this.FromDate = new Syncfusion.Windows.Forms.Tools.DateTimePickerAdv();
			this.label2 = new System.Windows.Forms.Label();
			this.label1 = new System.Windows.Forms.Label();
			this.cboType = new System.Windows.Forms.ComboBox();
			this.btnRun = new System.Windows.Forms.Button();
			this.dg = new System.Windows.Forms.DataGrid();
			this.btnExit = new System.Windows.Forms.Button();
			this.btnPrint = new System.Windows.Forms.Button();
			this.groupBox1.SuspendLayout();
			((System.ComponentModel.ISupportInitialize)(this.ToDate)).BeginInit();
			((System.ComponentModel.ISupportInitialize)(this.ToDate.Calendar)).BeginInit();
			((System.ComponentModel.ISupportInitialize)(this.FromDate)).BeginInit();
			((System.ComponentModel.ISupportInitialize)(this.FromDate.Calendar)).BeginInit();
			((System.ComponentModel.ISupportInitialize)(this.dg)).BeginInit();
			this.SuspendLayout();
			// 
			// groupBox1
			// 
			this.groupBox1.Controls.Add(this.Count);
			this.groupBox1.Controls.Add(this.label4);
			this.groupBox1.Controls.Add(this.ToDate);
			this.groupBox1.Controls.Add(this.label3);
			this.groupBox1.Controls.Add(this.FromDate);
			this.groupBox1.Controls.Add(this.label2);
			this.groupBox1.Controls.Add(this.label1);
			this.groupBox1.Controls.Add(this.cboType);
			this.groupBox1.Controls.Add(this.btnRun);
			this.groupBox1.Location = new System.Drawing.Point(8, 0);
			this.groupBox1.Name = "groupBox1";
			this.groupBox1.Size = new System.Drawing.Size(856, 56);
			this.groupBox1.TabIndex = 0;
			this.groupBox1.TabStop = false;
			this.groupBox1.Text = "Criteria";
			// 
			// Count
			// 
			this.Count.Location = new System.Drawing.Point(656, 24);
			this.Count.Name = "Count";
			this.Count.ReadOnly = true;
			this.Count.Size = new System.Drawing.Size(40, 20);
			this.Count.TabIndex = 13;
			// 
			// label4
			// 
			this.label4.Location = new System.Drawing.Point(616, 24);
			this.label4.Name = "label4";
			this.label4.Size = new System.Drawing.Size(40, 16);
			this.label4.TabIndex = 12;
			this.label4.Text = "Count:";
			// 
			// ToDate
			// 
			this.ToDate.BackgroundColor = new Syncfusion.Drawing.BrushInfo();
			this.ToDate.Border3DStyle = System.Windows.Forms.Border3DStyle.Flat;
			this.ToDate.BorderColor = System.Drawing.SystemColors.ControlDark;
			this.ToDate.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
			// 
			// 
			// 
			this.ToDate.Calendar.AllowMultipleSelection = false;
			this.ToDate.Calendar.Culture = new System.Globalization.CultureInfo("");
			this.ToDate.Calendar.DaysFont = new System.Drawing.Font("Verdana", 8F);
			this.ToDate.Calendar.DaysHeaderInterior = new Syncfusion.Drawing.BrushInfo(System.Drawing.SystemColors.Window);
			this.ToDate.Calendar.Dock = System.Windows.Forms.DockStyle.Fill;
			this.ToDate.Calendar.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
			this.ToDate.Calendar.GridLines = Syncfusion.Windows.Forms.Grid.GridBorderStyle.None;
			this.ToDate.Calendar.HeaderHeight = 20;
			this.ToDate.Calendar.HeaderStartColor = System.Drawing.Color.FromArgb(((int)(((byte)(254)))), ((int)(((byte)(128)))), ((int)(((byte)(62)))));
			this.ToDate.Calendar.Location = new System.Drawing.Point(0, 0);
			this.ToDate.Calendar.MinValue = new System.DateTime(1753, 1, 1, 0, 0, 0, 0);
			this.ToDate.Calendar.Name = "monthCalendar";
			// 
			// 
			// 
			this.ToDate.Calendar.NoneButton.Location = new System.Drawing.Point(131, 0);
			this.ToDate.Calendar.ScrollButtonSize = new System.Drawing.Size(24, 24);
			this.ToDate.Calendar.SelectedDates = new System.DateTime[] {
        new System.DateTime(2004, 12, 17, 9, 3, 47, 453)};
			this.ToDate.Calendar.Size = new System.Drawing.Size(203, 174);
			this.ToDate.Calendar.SizeToFit = true;
			this.ToDate.Calendar.Style = Syncfusion.Windows.Forms.VisualStyle.Office2003;
			this.ToDate.Calendar.TabIndex = 0;
			// 
			// 
			// 
			this.ToDate.Calendar.TodayButton.Location = new System.Drawing.Point(0, 0);
			this.ToDate.Calendar.TodayButton.Size = new System.Drawing.Size(131, 20);
			this.ToDate.Calendar.WeekFont = new System.Drawing.Font("Verdana", 8F);
			this.ToDate.Calendar.WeekInterior = new Syncfusion.Drawing.BrushInfo(Syncfusion.Drawing.GradientStyle.Vertical, System.Drawing.Color.PeachPuff, System.Drawing.Color.AntiqueWhite);
			this.ToDate.DropDownImage = null;
			this.ToDate.Format = System.Windows.Forms.DateTimePickerFormat.Short;
			this.ToDate.Location = new System.Drawing.Point(512, 24);
			this.ToDate.Name = "ToDate";
			this.ToDate.NullString = "";
			this.ToDate.ShowCheckBox = false;
			this.ToDate.Size = new System.Drawing.Size(88, 20);
			this.ToDate.Style = Syncfusion.Windows.Forms.VisualStyle.Office2003;
			this.ToDate.TabIndex = 11;
			this.ToDate.Value = new System.DateTime(2004, 12, 17, 9, 3, 47, 453);
			// 
			// label3
			// 
			this.label3.Location = new System.Drawing.Point(448, 24);
			this.label3.Name = "label3";
			this.label3.Size = new System.Drawing.Size(64, 16);
			this.label3.TabIndex = 10;
			this.label3.Text = "To date:";
			// 
			// FromDate
			// 
			this.FromDate.BackgroundColor = new Syncfusion.Drawing.BrushInfo();
			this.FromDate.Border3DStyle = System.Windows.Forms.Border3DStyle.Flat;
			this.FromDate.BorderColor = System.Drawing.SystemColors.ControlDark;
			this.FromDate.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
			// 
			// 
			// 
			this.FromDate.Calendar.AllowMultipleSelection = false;
			this.FromDate.Calendar.Culture = new System.Globalization.CultureInfo("");
			this.FromDate.Calendar.DaysFont = new System.Drawing.Font("Verdana", 8F);
			this.FromDate.Calendar.DaysHeaderInterior = new Syncfusion.Drawing.BrushInfo(System.Drawing.SystemColors.Window);
			this.FromDate.Calendar.Dock = System.Windows.Forms.DockStyle.Fill;
			this.FromDate.Calendar.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
			this.FromDate.Calendar.GridLines = Syncfusion.Windows.Forms.Grid.GridBorderStyle.None;
			this.FromDate.Calendar.HeaderHeight = 20;
			this.FromDate.Calendar.HeaderStartColor = System.Drawing.Color.FromArgb(((int)(((byte)(254)))), ((int)(((byte)(128)))), ((int)(((byte)(62)))));
			this.FromDate.Calendar.Location = new System.Drawing.Point(0, 0);
			this.FromDate.Calendar.MinValue = new System.DateTime(1753, 1, 1, 0, 0, 0, 0);
			this.FromDate.Calendar.Name = "monthCalendar";
			// 
			// 
			// 
			this.FromDate.Calendar.NoneButton.Location = new System.Drawing.Point(131, 0);
			this.FromDate.Calendar.ScrollButtonSize = new System.Drawing.Size(24, 24);
			this.FromDate.Calendar.SelectedDates = new System.DateTime[] {
        new System.DateTime(2004, 12, 17, 9, 3, 47, 453)};
			this.FromDate.Calendar.Size = new System.Drawing.Size(203, 174);
			this.FromDate.Calendar.SizeToFit = true;
			this.FromDate.Calendar.Style = Syncfusion.Windows.Forms.VisualStyle.Office2003;
			this.FromDate.Calendar.TabIndex = 0;
			// 
			// 
			// 
			this.FromDate.Calendar.TodayButton.Location = new System.Drawing.Point(0, 0);
			this.FromDate.Calendar.TodayButton.Size = new System.Drawing.Size(131, 20);
			this.FromDate.Calendar.WeekFont = new System.Drawing.Font("Verdana", 8F);
			this.FromDate.Calendar.WeekInterior = new Syncfusion.Drawing.BrushInfo(Syncfusion.Drawing.GradientStyle.Vertical, System.Drawing.Color.PeachPuff, System.Drawing.Color.AntiqueWhite);
			this.FromDate.DropDownImage = null;
			this.FromDate.Format = System.Windows.Forms.DateTimePickerFormat.Short;
			this.FromDate.Location = new System.Drawing.Point(344, 24);
			this.FromDate.Name = "FromDate";
			this.FromDate.NullString = "";
			this.FromDate.ShowCheckBox = false;
			this.FromDate.Size = new System.Drawing.Size(88, 20);
			this.FromDate.Style = Syncfusion.Windows.Forms.VisualStyle.Office2003;
			this.FromDate.TabIndex = 9;
			this.FromDate.Value = new System.DateTime(2004, 12, 17, 9, 3, 47, 453);
			// 
			// label2
			// 
			this.label2.Location = new System.Drawing.Point(280, 24);
			this.label2.Name = "label2";
			this.label2.Size = new System.Drawing.Size(64, 16);
			this.label2.TabIndex = 8;
			this.label2.Text = "From date:";
			// 
			// label1
			// 
			this.label1.Location = new System.Drawing.Point(96, 24);
			this.label1.Name = "label1";
			this.label1.Size = new System.Drawing.Size(40, 16);
			this.label1.TabIndex = 7;
			this.label1.Text = "Type:";
			// 
			// cboType
			// 
			this.cboType.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
			this.cboType.Items.AddRange(new object[] {
            "All",
            "Protocols",
            "All Visits",
            "Admissions",
            "Out-Patient Visits",
            "All Ancillaries",
            "Admission Ancillaries",
            "Out-Patient Ancillaries"});
			this.cboType.Location = new System.Drawing.Point(144, 24);
			this.cboType.Name = "cboType";
			this.cboType.Size = new System.Drawing.Size(121, 21);
			this.cboType.TabIndex = 6;
			// 
			// btnRun
			// 
			this.btnRun.Location = new System.Drawing.Point(8, 25);
			this.btnRun.Name = "btnRun";
			this.btnRun.Size = new System.Drawing.Size(75, 23);
			this.btnRun.TabIndex = 5;
			this.btnRun.Text = "Run";
			this.btnRun.Click += new System.EventHandler(this.btnRun_Click);
			// 
			// dg
			// 
			this.dg.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
						| System.Windows.Forms.AnchorStyles.Left)
						| System.Windows.Forms.AnchorStyles.Right)));
			this.dg.DataMember = "";
			this.dg.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.dg.Location = new System.Drawing.Point(0, 64);
			this.dg.Name = "dg";
			this.dg.Size = new System.Drawing.Size(872, 344);
			this.dg.TabIndex = 2;
			// 
			// btnExit
			// 
			this.btnExit.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
			this.btnExit.Location = new System.Drawing.Point(368, 416);
			this.btnExit.Name = "btnExit";
			this.btnExit.Size = new System.Drawing.Size(75, 23);
			this.btnExit.TabIndex = 5;
			this.btnExit.Text = "Exit";
			this.btnExit.Click += new System.EventHandler(this.btnExit_Click);
			// 
			// btnPrint
			// 
			this.btnPrint.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
			this.btnPrint.Location = new System.Drawing.Point(280, 416);
			this.btnPrint.Name = "btnPrint";
			this.btnPrint.Size = new System.Drawing.Size(75, 23);
			this.btnPrint.TabIndex = 4;
			this.btnPrint.Text = "Print";
			this.btnPrint.Click += new System.EventHandler(this.btnPrint_Click);
			// 
			// ReportDataIntegrity
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(872, 446);
			this.Controls.Add(this.btnExit);
			this.Controls.Add(this.btnPrint);
			this.Controls.Add(this.dg);
			this.Controls.Add(this.groupBox1);
			this.Name = "ReportDataIntegrity";
			this.Text = "Data Integrity Report - Visits";
			this.groupBox1.ResumeLayout(false);
			this.groupBox1.PerformLayout();
			((System.ComponentModel.ISupportInitialize)(this.ToDate.Calendar)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.ToDate)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.FromDate.Calendar)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.FromDate)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.dg)).EndInit();
			this.ResumeLayout(false);

		}
		#endregion

		private void btnRun_Click(object sender, System.EventArgs e)
		{
			btnPrint.Enabled = false;
			int typeCriteria = cboType.SelectedIndex;
			StringBuilder sb = new StringBuilder();
			StringBuilder sbMessage = new StringBuilder();

			if (!VerifyCriteria(ref sbMessage))
			{
				MessageBox.Show(this, sbMessage.ToString(), "Enter All Criteria", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
				return;
			}

			this._dtReport.Clear();

			switch (this.cboType.SelectedIndex)
			{
				case 0: //all					
					VerifyProtocols();
					VerifyAdmissions();
					VerifyOutPatientVisits();
					VerifyAdmissionAncillaries();
					VerifyOutPatientAncillaries();
					break;
				case 1:	//protocols (for completeness)
					VerifyProtocols();
					break;
				case 2: // all visits					
					VerifyAdmissions();
					VerifyOutPatientVisits();
					break;
				case 3:
					VerifyAdmissions();
					break;
				case 4:
					VerifyOutPatientVisits();
					break;
				case 5:	//all ancillaries
					VerifyAdmissionAncillaries();
					VerifyOutPatientAncillaries();
					break;
				case 6:
					VerifyAdmissionAncillaries();
					break;
				case 7:
					VerifyOutPatientAncillaries();
					break;
			}

			dg.DataSource = null;
			this._dvReport = new DataView(this._dtReport);
			dg.DataSource = this._dvReport;
			Count.Text = this._dtReport.Rows.Count.ToString();
			if (this._dtReport.Rows.Count > 0)
				btnPrint.Enabled = true;


		}

		private bool VerifyProtocols()
		{
			StringBuilder sb = new StringBuilder();
			Cursor.Current = Cursors.WaitCursor;
			StringBuilder sql = new StringBuilder("SELECT ID, ProtocolNumber, IrbApproval, IrbRenewal, Incomplete FROM Protocol");

			//SqlConnection conn = LoginForm.GetSqlConnection();			
			IDataReader reader = null;
			int id = 0;
			string protoNum = "";
			bool incomplete;
			DataRow row;

			try
			{
				reader = DataAccess.GetDataReader(sql.ToString());
				while (reader.Read())
				{
					row = this._dtReport.NewRow();
					id = reader.GetInt32(0);

					protoNum = reader.GetString(1);
					incomplete = false;
					if (!reader.IsDBNull(4))
						incomplete = reader.GetBoolean(4);

					if (incomplete)
					{
						row["ID"] = id;
						row["Type"] = "Protocol";
						row["ReferenceID"] = "Proto Num: " + protoNum;
						row["VisitDate"] = System.DBNull.Value;
						row["Message"] = "Protocol is incomplete.";
						this._dtReport.Rows.Add(row);
					}
				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Could not retrieve protocol info!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this, sb.ToString(), "Retrieve Protocol Info Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return false;
			}
			finally
			{
				if (reader != null)
				{
					if (!reader.IsClosed)
						reader.Close();
				}
				//if(conn != null)
				//    conn.Dispose();
			}

		}

		private bool VerifyAdmissionAncillaries()
		{
			StringBuilder sb = new StringBuilder();
			Cursor.Current = Cursors.WaitCursor;
			StringBuilder sql = new StringBuilder("SELECT ID,TestDate,Visit FROM AncillaryAdmission");

			string criteria = "";
			if (!(FromDate.IsNullDate && ToDate.IsNullDate))
			{
				criteria = " WHERE TestDate BETWEEN '" + FromDate.Value.Date + "' AND '"
					+ ToDate.Value.Date + "'";
			}
			else
			{
				if (FromDate.IsNullDate)
					criteria = " WHERE TestDate < '" + ToDate.Value.Date + "'";
				else
					criteria = " WHERE TestDate > '" + FromDate.Value.Date + "'";
			}

			if (criteria.Length > 0)
				sql.Append(criteria);

			sql.Append(" ORDER BY TestDate");

			//SqlConnection conn = LoginForm.GetSqlConnection();
			IDataReader reader = null;
			int id = 0;
			int visit = 0;
			DateTime testDate;
			DataRow row;

			try
			{
				reader = DataAccess.GetDataReader(sql.ToString());
				while (reader.Read())
				{
					row = this._dtReport.NewRow();
					id = reader.GetInt32(0);

					testDate = new DateTime(1);
					if (!reader.IsDBNull(1))
						testDate = reader.GetDateTime(1);

					visit = reader.GetInt32(2);

					row = this._dtReport.NewRow();
					row["ID"] = id;
					row["Type"] = "Admission Ancillary";
					row["ReferenceID"] = "Visit ID: " + visit.ToString();
					row["VisitDate"] = testDate;

					if (!ValidateAdmissionVisit(visit, testDate, row))
						this._dtReport.Rows.Add(row);
				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Could not retrieve ancillary info!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this, sb.ToString(), "Retrieve Ancillary Info Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return false;
			}
			finally
			{
				if (reader != null)
				{
					if (!reader.IsClosed)
						reader.Close();
				}
				//if(conn != null)
				//    conn.Dispose();
			}
		}

		private bool VerifyOutPatientAncillaries()
		{
			StringBuilder sb = new StringBuilder();
			Cursor.Current = Cursors.WaitCursor;
			StringBuilder sql = new StringBuilder("SELECT ID,TestDate,Visit FROM AncillaryOutPatient");

			string criteria = "";
			if (!(FromDate.IsNullDate && ToDate.IsNullDate))
			{
				criteria = " WHERE TestDate BETWEEN '" + FromDate.Value.Date + "' AND '"
					+ ToDate.Value.Date + "'";
			}
			else
			{
				if (FromDate.IsNullDate)
					criteria = " WHERE TestDate < '" + ToDate.Value.Date + "'";
				else
					criteria = " WHERE TestDate > '" + FromDate.Value.Date + "'";
			}

			if (criteria.Length > 0)
				sql.Append(criteria);

			sql.Append(" ORDER BY TestDate");

			//SqlConnection conn = LoginForm.GetSqlConnection();
			IDataReader reader = null;
			int id = 0;
			int visit = 0;
			DateTime testDate;
			DataRow row;

			try
			{
				reader = DataAccess.GetDataReader(sql.ToString());
				while (reader.Read())
				{
					row = this._dtReport.NewRow();
					id = reader.GetInt32(0);

					testDate = new DateTime(1);
					if (!reader.IsDBNull(1))
						testDate = reader.GetDateTime(1);

					visit = reader.GetInt32(2);

					row = this._dtReport.NewRow();
					row["ID"] = id;
					row["Type"] = "Out-Patient Ancillary";
					row["ReferenceID"] = "Visit ID: " + visit.ToString();
					row["VisitDate"] = testDate;

					if (!ValidateOutPatientVisit(visit, testDate, row))
						this._dtReport.Rows.Add(row);
				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Could not retrieve ancillary info!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this, sb.ToString(), "Retrieve Ancillary Info Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return false;
			}
			finally
			{
				if (reader != null)
				{
					if (!reader.IsClosed)
						reader.Close();
				}
				//if(conn != null)
				//    conn.Dispose();
			}
		}

		private bool ValidateAdmissionVisit(int visitID, DateTime testDate, DataRow row)
		{
			//get the visit and verify
			StringBuilder sb = new StringBuilder();
			Cursor.Current = Cursors.WaitCursor;
			StringBuilder sql = new StringBuilder("SELECT Pending,VisitDate,DischargeDate,Protocol FROM ProtocolVisitAdmission ");
			sql.Append(" WHERE ID=" + visitID);

			//SqlConnection conn = LoginForm.GetSqlConnection();
			IDataReader reader = null;
			int protocol = 0;
			bool pending = false;
			DateTime admit;
			DateTime discharge;

			try
			{
				reader = DataAccess.GetDataReader(sql.ToString());

				if (!reader.Read())
				{
					row["Message"] = "Could not find referring visit.";
					return false;
				}

				pending = false;
				if (!reader.IsDBNull(0))
					pending = reader.GetBoolean(0);

				if (pending)
				{
					row["Message"] = "Admission is pending.";
					return false;
				}

				admit = reader.GetDateTime(1);

				if (admit.CompareTo(testDate) > 0)
				{
					row["Message"] = "Admit date is greater than test date.";
					return false;
				}

				discharge = reader.GetDateTime(2);

				if (discharge.CompareTo(testDate) < 0)
				{
					row["Message"] = "Discharge date is less than test date.";
					return false;
				}

				protocol = reader.GetInt32(3);
				if (!ValidateProtocol(protocol, visitID, testDate, row))
				{
					row["Message"] = "Protocol ID: " + protocol + " - " + row["Message"];
					return false;
				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Could not retrieve visit info!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this, sb.ToString(), "Retrieve Visit Info Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return false;
			}
			finally
			{
				if (reader != null)
				{
					if (!reader.IsClosed)
						reader.Close();
				}
				//if(conn != null)
				//    conn.Dispose();
			}

		}

		private bool ValidateOutPatientVisit(int visitID, DateTime testDate, DataRow row)
		{
			//get the visit and verify
			StringBuilder sb = new StringBuilder();
			Cursor.Current = Cursors.WaitCursor;
			StringBuilder sql = new StringBuilder("SELECT ID,VisitDate,Protocol FROM ProtocolVisitOutPatient ");
			sql.Append(" WHERE ID=" + visitID);

			//SqlConnection conn = LoginForm.GetSqlConnection();			
			IDataReader reader = null;
			int protocol = 0;
			string visit;

			try
			{
				reader = DataAccess.GetDataReader(sql.ToString());

				if (!reader.Read())
				{
					row["Message"] = "Could not find referring visit.";
					return false;
				}

				visit = reader.GetDateTime(1).ToShortDateString();
				string test = testDate.ToShortDateString();
				if (visit != test)
				{
					row["Message"] = "Test date does not equal visit for referring visit.";
					return false;
				}

				protocol = reader.GetInt32(2);
				if (!ValidateProtocol(protocol, visitID, testDate, row))
				{
					row["Message"] = "Protocol ID: " + protocol + " - " + row["Message"];
					return false;
				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Could not retrieve visit info!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this, sb.ToString(), "Retrieve Visit Info Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return false;
			}
			finally
			{
				if (reader != null)
				{
					if (!reader.IsClosed)
						reader.Close();
				}
				//if(conn != null)
				//    conn.Dispose();
			}

		}

		private bool VerifyOutPatientVisits()
		{
			StringBuilder sb = new StringBuilder();
			Cursor.Current = Cursors.WaitCursor;
			StringBuilder sql = new StringBuilder("SELECT ID,VisitDate,Protocol FROM ProtocolVisitOutPatient");

			string criteria = "";
			if (!(FromDate.IsNullDate && ToDate.IsNullDate))
			{
				criteria = " WHERE VisitDate BETWEEN '" + FromDate.Value.Date + "' AND '"
					+ ToDate.Value.Date + "'";
			}
			else
			{
				if (FromDate.IsNullDate)
					criteria = " WHERE VisitDate < '" + ToDate.Value.Date + "'";
				else
					criteria = " WHERE VisitDate > '" + FromDate.Value.Date + "'";
			}

			if (criteria.Length > 0)
				sql.Append(criteria);

			sql.Append(" ORDER BY VisitDate");

			//SqlConnection conn = LoginForm.GetSqlConnection();
			IDataReader reader = null;
			int id = 0;
			int protocol = 0;
			DateTime visit;
			DataRow dr;

			try
			{
				reader = DataAccess.GetDataReader(sql.ToString());
				while (reader.Read())
				{
					id = reader.GetInt32(0);

					visit = new DateTime(1);
					if (!reader.IsDBNull(1))
						visit = reader.GetDateTime(1);

					protocol = reader.GetInt32(2);

					dr = this._dtReport.NewRow();
					dr["ID"] = id;
					dr["Type"] = "Out-Patient Visit";
					dr["ReferenceID"] = "Proto ID: " + protocol.ToString();

					if (visit.ToShortDateString() == "1/1/0001")
					{
						dr["VisitDate"] = System.DBNull.Value;
						dr["Message"] = "Visit date is not entered.";
						this._dtReport.Rows.Add(dr);
						continue;
					}

					if (!ValidateProtocol(protocol, id, visit, dr))
					{

						dr["VisitDate"] = visit;
						this._dtReport.Rows.Add(dr);
					}
				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Could not retrieve visit info!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this, sb.ToString(), "Retrieve Visit Info Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return false;
			}
			finally
			{
				if (reader != null)
				{
					if (!reader.IsClosed)
						reader.Close();
				}
				//if(conn != null)
				//    conn.Dispose();
			}
		}

		private bool VerifyAdmissions()
		{
			StringBuilder sb = new StringBuilder();
			Cursor.Current = Cursors.WaitCursor;
			StringBuilder sql = new StringBuilder("SELECT ID,Pending,VisitDate,DischargeDate,Protocol FROM ProtocolVisitAdmission");

			string criteria = "";
			if (!(FromDate.IsNullDate && ToDate.IsNullDate))
			{
				criteria = " WHERE VisitDate BETWEEN '" + FromDate.Value.Date + "' AND '"
					+ ToDate.Value.Date + "'";
			}
			else
			{
				if (FromDate.IsNullDate)
					criteria = " WHERE VisitDate < '" + ToDate.Value.Date + "'";
				else
					criteria = " WHERE VisitDate > '" + FromDate.Value.Date + "'";
			}

			if (criteria.Length > 0)
				sql.Append(criteria);

			sql.Append(" ORDER BY VisitDate");

			//SqlConnection conn = LoginForm.GetSqlConnection();
			IDataReader reader = null;
			int id = 0;
			int protocol = 0;
			bool pending = false;
			DateTime visit;
			DateTime discharge;
			DataRow dr;

			try
			{
				reader = DataAccess.GetDataReader(sql.ToString());
				while (reader.Read())
				{
					id = reader.GetInt32(0);

					pending = false;
					if (!reader.IsDBNull(1))
						pending = reader.GetBoolean(1);

					visit = new DateTime(0);
					if (!reader.IsDBNull(2))
						visit = reader.GetDateTime(2);

					discharge = new DateTime(0);
					if (!reader.IsDBNull(3))
						discharge = reader.GetDateTime(3);

					protocol = reader.GetInt32(4);

					dr = this._dtReport.NewRow();
					dr["ID"] = id;
					dr["Type"] = "Admission";
					dr["ReferenceID"] = "Proto ID: " + protocol.ToString();
					if (pending)
					{
						dr["VisitDate"] = System.DBNull.Value;
						dr["Message"] = "Admission is pending.";
						this._dtReport.Rows.Add(dr);
						continue;
					}
					if (visit.ToShortDateString() == "1/1/0001")
					{
						dr["VisitDate"] = System.DBNull.Value;
						dr["Message"] = "Admit date is not entered.";
						this._dtReport.Rows.Add(dr);
						continue;
					}
					if (discharge.ToShortDateString() == "1/1/0001")
					{
						dr["VisitDate"] = System.DBNull.Value;
						dr["Message"] = "Discharge date is not entered.";
						this._dtReport.Rows.Add(dr);
						continue;
					}

					if (!ValidateProtocol(protocol, id, discharge, dr))
					{

						dr["VisitDate"] = visit;
						this._dtReport.Rows.Add(dr);
					}
				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Could not retrieve visit info!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this, sb.ToString(), "Retrieve Visit Info Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return false;
			}
			finally
			{
				if (reader != null)
				{
					if (!reader.IsClosed)
						reader.Close();
				}
				//if(conn != null)
				//    conn.Dispose();
			}
		}

		private bool ValidateProtocol(int protocol, int id, DateTime visit, DataRow row)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT p.IrbApproval,p.IrbRenewal,p.Incomplete " +
				" FROM Protocol p" +
				" WHERE p.ID = " + protocol;

			bool incomplete;
			DateTime dtApp = new DateTime(0);
			DateTime dtRen = new DateTime(0);
			DateTime dtLatest;

			//SqlConnection conn = LoginForm.GetSqlConnection();
			IDataReader reader = null;
			try
			{
				reader = DataAccess.GetDataReader(sql);

				if (reader.Read())
				{
					incomplete = false;
					if (!reader.IsDBNull(2))
						incomplete = reader.GetBoolean(2);
					if (incomplete)
					{
						row["Message"] = "Protocol is incomplete.";
						return false;
					}

					if (!reader.IsDBNull(0))
						dtApp = reader.GetDateTime(0);
					if (!reader.IsDBNull(1))
						dtRen = reader.GetDateTime(1);

					if (dtApp.ToShortDateString() == "1/1/0001" && dtRen.ToShortDateString() == "1/1/0001")
					{
						row["Message"] = "Both protocol Irb Approval and Renewal dates are null.";
						return false;
					}

					if (dtApp.ToShortDateString() == "1/1/0001" || dtRen.ToShortDateString() == "1/1/0001")
					{
						if (dtApp.ToShortDateString() == "1/1/0001")
							dtLatest = dtRen;
						else
							dtLatest = dtApp;
					}
					else
					{
						dtLatest = dtApp;

						if (dtRen.CompareTo(dtApp) > 0)
							dtLatest = dtRen;
					}

					int addDays = 364;
					int year = dtLatest.Year;
					if (DateTime.IsLeapYear(year))
						addDays = 365;

					dtLatest = dtLatest.AddDays(addDays);

					if (visit.CompareTo(dtLatest) > 0)
					{
						row["Message"] = "Protocol has expired.  Expiration date is " + dtLatest.ToShortDateString();
						return false;
					}
					return true;
				}
				else
				{
					sb.Append("This should not happen.");
					sb.Append(Environment.NewLine);
					sb.Append("Function ValidateProtocol.");
					sb.Append(Environment.NewLine);
					sb.Append(Constants.NotifySupportImmediate);
					Exception ex = new Exception(sb.ToString());
					////ExceptionPolicy.HandleException(ex, "Exception Policy");
					row["Message"] = sb.ToString();
					return false;
				}
			}
			catch (Exception ex)
			{
				sb.Append("Could not retrieve protocol for validation!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this, sb.ToString(), "Retrieve Protocol For Validation Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				row["Message"] = sb.ToString();
				return false;
			}
			finally
			{
				if (reader != null)
				{
					if (!reader.IsClosed)
						reader.Close();
				}
				//if(conn != null)
				//    conn.Dispose();
			}
		}

		private bool VerifyCriteria(ref StringBuilder sbMessage)
		{
			//If from date is null, assume from beginning
			//if to date is null, assume today
			return true;
		}

		private void btnExit_Click(object sender, System.EventArgs e)
		{
			this.Close();
		}

		private void btnPrint_Click(object sender, System.EventArgs e)
		{
			StringBuilder sbCrit = new StringBuilder();

			sbCrit.Append("Criteria: " + cboType.Text);
			string fromDate = "";
			if (!FromDate.IsNullDate)
				fromDate = FromDate.Value.ToShortDateString();
			string toDate = "";
			if (!ToDate.IsNullDate)
				toDate = ToDate.Value.ToShortDateString();

			sbCrit.Append("  From Date: " + fromDate);
			sbCrit.Append("  To Date: " + toDate);
			sbCrit.Append("  Count: " + Count.Text);
			string[] title = new string[2];
			title[0] = "GCRC - Data Integrity Report";
			title[1] = sbCrit.ToString();
			DataView dv = (DataView)dg.DataSource;
			PrintingUtils pu = new PrintingUtils(dv, title, this, false);
			pu.Initialize();
		}
	}
}
